package controllers.dbWrapper;

import java.sql.*;
import java.util.ArrayList;
import java.util.GregorianCalendar;

import controllers.ExceptionsTypes;
import person.Person;

public class PersonWrapper{
	
	
	public static ArrayList<Person> getAllPerson(boolean closeConnection) throws Exception{
		ArrayList<Person> people = new ArrayList<Person>();
	    String query = "SELECT * "  +
	                   "FROM "+ DBWrapper.getDbname() +".person ";
	   
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        while (rs.next()) {
	        	long birthDay = rs.getLong("birth_date");
	        	GregorianCalendar bDate = new GregorianCalendar();
	        	bDate.setTimeInMillis(birthDay);
	        	people.add(new Person(
	        			rs.getString("name"), 
	        			rs.getString("surname"), 
	        			rs.getString("tax_code"), 
	        			rs.getString("address"), 
	        			rs.getString("phone_number"), 
	        			rs.getString("fax"), 
	        			rs.getString("email"),
	        			bDate));
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	   
	    return people;
	}
	
	public static Person getPerson(String taxCode, boolean closeConnection) throws Exception{
		Person pers = null;
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".person " +
	    		"WHERE tax_code = '"+taxCode+"'";
	    
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        if(rs.next()){
	        	
	        	long birthDay = rs.getLong("birth_date");
	        	GregorianCalendar bDate = new GregorianCalendar();
	        	bDate.setTimeInMillis(birthDay);
	        	pers = new Person(
	        			rs.getString("name"), 
	        			rs.getString("surname"), 
	        			rs.getString("tax_code"), 
	        			rs.getString("address"), 
	        			rs.getString("phone_number"), 
	        			rs.getString("fax"),
	        			rs.getString("email"),
	        			bDate);
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	   
	    if(pers == null)
	    	throw new Exception(ExceptionsTypes.NOT_FOUND.name());
	    return pers;
	}

	public static void insertPerson(boolean closeConnection, Person ... people) throws Exception{
		Statement stmt = DBWrapper.getConnection().createStatement();
		for(int i = 0 ; i<people.length; i++){
			Person pers = people[i];
	        int rows =stmt.executeUpdate(
		        	"INSERT INTO "+ DBWrapper.getDbname()+".person (tax_code, name, surname, address, email, fax, phone_number, birth_date)" +
		            "VALUES('" + 
		            pers.getTaxCode() + "', '" +
		            pers.getName() +"', '" +
		            pers.getSurname() + "', '" + 
		            pers.getAddress() + "', '" +
		            pers.getEmail() + "', '" + 
		            pers.getFax() + "', '" +
		            pers.getPhoneNumber() + "', '" +
		            pers.getBirthDate().getTimeInMillis() + "')");
	        if(rows == 0)
	    	    throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
		}
        stmt.close();
        if(closeConnection)
        	DBWrapper.getConnection().close();
	}

	public static void deletePerson(boolean closeConnection, Person person) throws Exception{
	    String query = "DELETE "  +
	    		"FROM "+ DBWrapper.getDbname() +".person " +
	    		"WHERE tax_code = '"+person.getTaxCode()+"'";
    	Statement stmt = DBWrapper.getConnection().createStatement();
        int rows = stmt.executeUpdate(query);
        if(rows>1){
        	throw new SQLException();
        }else if(rows == 0)
    	    throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
        stmt.close();
        if(closeConnection)
        	DBWrapper.getConnection().close();
	}

}
